package utilities;

import infoholders.Appointment;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import people.Nurse;
import people.Patient;

public class Database {
	private static ArrayList<String> usernames = new ArrayList<String>();

	/**
	 * Database login name
	 */
	private static final String DATABASE_LOGIN = "root";

	/**
	 * Database password
	 */
	private static final String DATABSE_PASSWORD = "root";

	/**
	 * URL for the database
	 */
	private static final String DATABSE_URL = "jdbc:mysql://localhost/doctorsoffice";

	/**
	 * Instance of a connection
	 */
	private static Connection conn;

	/**
	 * Array of tables that refer to people
	 */
	private final static String[] peopleTables = { "patient", "nurse",
			"doctor", "admin" };

	/**
	 * Default constructor for the database
	 */
	public Database() {
		connect();
	}

	public static Connection connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(DATABSE_URL, DATABASE_LOGIN,
					DATABSE_PASSWORD);
		} catch (ClassNotFoundException ex) {
			System.err.println(ex.getMessage());
		} catch (IllegalAccessException ex) {
			System.err.println(ex.getMessage());
		} catch (InstantiationException ex) {
			System.err.println(ex.getMessage());
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return conn;
	}

	public static Connection getConnection() {
		return conn;
	}

	public static void closeConnection() {
		try {
			conn.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
	}

	/**
	 * Turns a string array to a comma separated list
	 * 
	 * @param arr
	 *            String array
	 * @return string of elements separated by commas
	 */
	private String arrayToCSV(String[] arr) {
		String result = "";

		for (String str : arr) {
			result += str + ",";
		}
		// delete the last comma
		result = result.substring(0, result.length() - 1);

		return result;
	}

	/**
	 * Checks to see if a username is taken on the database
	 * 
	 * @param username
	 *            Username to check
	 * @return true if the username is unique, false otherwise
	 */
	public static boolean userNameAvailable(String username) {
		// connect to the database
		connect();

		// generate a query that includes all user tables
		String query = "";
		String delimiter = " UNION ";

		for (String str : peopleTables) {
			query += "SELECT id FROM " + str + " WHERE username = '" + username
					+ "'" + delimiter;
		}
		// delete the last delimiter
		query = query.substring(0, query.length() - delimiter.length());

		// TODO: Remove debug line when done
		System.out.println("Executing query: " + query);

		// try to execute the query
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);

			// if the result set is populated at all,
			// the username is taken
			if (rs.next()) {
				return false;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} finally {
			closeConnection();
		}
		return true;
	}

	/**
	 * This is an obsolete function that only works for generic types
	 * 
	 * @param attributes
	 * @param table
	 * @param specifications
	 * @return
	 */
	public List<Object[]> loadEntity(String[] attributes, String table,
			String[] specifications) {
		// begin the query
		String query = "SELECT";

		// list the attributes on the query
		for (int i = 0; i < attributes.length; i++) {
			query += " " + attributes[i] + ",";
		}
		// delete the last comma
		query = query.substring(0, query.length() - 1);

		// add FROM [table] to the query
		query += " FROM " + table;

		// add specifications
		if (specifications != null) {
			query += " WHERE";
			for (int i = 0; i < specifications.length; i++) {
				query += " " + specifications[i] + ",";
			}
			// delete the last comma
			query = query.substring(0, query.length() - 1);
		}

		// TODO: Remove debug lines when done
		System.out.println();
		System.out.println("Query: " + query);
		System.out.println();

		// create a list to hold all the rows
		List<Object[]> totalList = new ArrayList<Object[]>();

		// try to load the objects from the query
		try {
			// create the query
			Statement st = conn.createStatement();
			// execute query
			ResultSet rs = st.executeQuery(query);

			// load information from the query
			while (rs.next()) {
				// create an array to hold individual rows
				Object[] results = new Object[attributes.length];

				// traverse the results
				for (int i = 0; i < results.length; i++) {
					// retrieve the row data from the attribute
					results[i] = rs.getObject(attributes[i]);
				}
				// add the row data to the list
				totalList.add(results);
			}
		} catch (SQLException ex) {
			System.out
					.println("-------Something went wrong with loadEntity...------");
			ex.printStackTrace();
			System.err.println(ex.getMessage());
			System.out
					.println("----------------------------------------------------");
		}

		// return it
		return totalList;
	}

	/**
	 * Loads a patient object from the database to an object.
	 * 
	 * @param id
	 *            Database id of the patient
	 * @return the instance of the patient
	 */
	public Patient loadPatient(String username) {
		Patient p = new Patient(username, username);

		String query = "SELECT first_name, last_name, password "
				+ "FROM patient where username ='" + username + "'";
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				// get the info from the database
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				String encryptPass = rs.getString("password");

				// load it into the object
				p.setFirstName(firstName);
				p.setLastName(lastName);
				p.setUserPass(encryptPass);
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// return it
		return p;
	}

	/**
	 * Loads a Nurse object from the database based on its corresponding id on
	 * the database
	 * 
	 * @param id
	 *            database id number
	 * @return the nurse instance
	 */
	public Nurse loadNurse(String username) {
		Nurse n = new Nurse("", "");

		String query = "SELECT first_name, last_name, password, patient"
				+ " FROM nurse where username ='" + username + "'";
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				// get the info from the database
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				String encryptPass = rs.getString("password");
				String currentPatient = rs.getString("patient");

				// load it into the object
				n.setFirstName(firstName);
				n.setLastName(lastName);
				n.setUserPass(encryptPass);
				n.setCurrentPatientUsername(currentPatient);

				// we already knew the username from this method's parameter
				n.setUserID(username);
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// return it
		return n;
	}

	/**
	 * Loads an appointment from the database
	 * 
	 * @return the desired appointment
	 */
	public Appointment loadAppointment() {
		// TODO: this entire method
		Appointment p = null;

		return p;
	}

	/**
	 * Goes through all user tables and deletes a user by their username
	 * 
	 * @param username
	 *            the username of the user to delete
	 */
	public static void deleteByUsername(String username) {
		connect();
		try {
			// Create a query for deletion
			Statement st = conn.createStatement();

			// Execute the queries
			for (String str : peopleTables) {
				st.executeUpdate("DELETE FROM " + str + " WHERE username='"
						+ username + "'");
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} finally {
			// close the connection
			closeConnection();
		}
	}

	/**
	 * Executes the specified update query
	 * 
	 * @param query
	 *            The query to be executed
	 * @return true if successful, false if there were errors
	 */
	public static boolean executeUpdateQuery(String query) {
		Connection conn = getConnection();

		try {
			Statement st = conn.createStatement();
			st.executeUpdate(query);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			closeConnection();
		}

		return true;
	}

	public static void resetDatabase() {
		usernames.clear();
	}
}
